﻿using System;
using System.Diagnostics;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace LinWon.Core.Data
{
    /// <summary>
    /// MsSql常用操作类
    /// </summary>
    public sealed class SqlHelper
    {
        private string _keyNameByConn = "ConnString";
        private string _connString = String.Empty;

        /// <summary>
        /// 配置文件中连接字符串的关键字，默认值为ConnString
        /// </summary>
        public string KeyNameByConnString
        {
            get { return _keyNameByConn; }
            set
            {
                _connString = String.Empty;
                _keyNameByConn = value;
            }
        }

        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnString
        {
            get
            {
                if (String.IsNullOrEmpty(_connString))
                {
                    _connString = ConfigurationManager.ConnectionStrings[KeyNameByConnString].ConnectionString;
                }
                return _connString;
            }
            set { _connString = value; }
        }

        /// <summary>
        /// MsSql常用操作类
        /// </summary>
        /// <param name="connStr">连接字符串</param>
        public SqlHelper(string connStr)
        {
            this.ConnString = connStr;
        }

        /// <summary>
        /// 执行存储过程，并有Int型的返回值
        /// </summary>       
        /// <param name="cmdText">命令</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>执行结果</returns>
        public int RunProcReturnInt(string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnString);
            int val;

            try
            {
                PrepareCommand(cmd, conn, CommandType.StoredProcedure, cmdText, cmdParms);
                cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                val = (int)cmd.Parameters["@RETURN_VALUE"].Value;
                cmd.Parameters.Clear();
            }            
            finally
            {
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            return val;
        }

        public int RunProc(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteNonQuery(CommandType.StoredProcedure, cmdText, cmdParms);
        }

        /// <summary>
        /// 执行存储过程，并有1个返回参数
        /// </summary>      
        /// <param name="cmdText">命令</param>
        /// <param name="outParm">返回参数名</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>结果</returns>
        public object RunProcObject(string cmdText, string outParm, params SqlParameter[] cmdParms)
        {
            object obj;
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnString);

            try
            {
                PrepareCommand(cmd, conn, CommandType.StoredProcedure, cmdText, cmdParms);
                cmd.Parameters[outParm].Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                obj = cmd.Parameters[outParm].Value;
                cmd.Parameters.Clear();
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
            }

            return obj;
        }

        /// <summary>
        /// 执行SQL指令,返回第一行第一列的值
        /// </summary>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">命令</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>执行结果</returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            object obj = null;
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnString);
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
            }

            return obj;
        }

        /// <summary>
        /// 执行SQL指令,返回第一行第一列的值
        /// </summary>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">命令</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>执行结果</returns>
        public object ExecuteScalar(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteScalar(CommandType.Text, cmdText, cmdParms);
        }

        /// <summary>
        /// 执行返回DataReader型的SQL指令
        /// </summary>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">命令</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnString);
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch (SqlException)
            {                
                throw;
            }
        }

        /// <summary>
        /// 执行返回DataReader型的SQL指令
        /// </summary>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">命令</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteReader(CommandType.Text, cmdText, cmdParms);
        }

        /// <summary>
        /// 该方法仅为需要在事务中进行查询操作提供
        /// </summary>     
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">SQL命令</param>
        /// <param name="conn">事务连接,不能为NULL</param>
        /// <param name="tran">事务,不能为NULL</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReaderByTransaction(System.Data.CommandType cmdType, string cmdText, SqlTransaction tran, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();

            try
            {
                PrepareCommand(cmd, tran.Connection, cmdType, cmdText, cmdParms);

                cmd.Transaction = tran;

                SqlDataReader rdr = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return rdr;
            }
            finally
            {
                cmd.Dispose();
            }
        }

        /// <summary>
        /// 执行返回DataSet的SQL指令
        /// </summary>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">命令</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnString);
            SqlDataAdapter dap = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();

            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                dap.Fill(ds);
                cmd.Parameters.Clear();
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
                dap.Dispose();
                conn.Dispose();
            }

            return ds;
        }

        /// <summary>
        /// 执行返回DataSet的SQL指令
        /// </summary>        
        /// <param name="cmdText">命令</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteDataSet(CommandType.Text, cmdText, cmdParms);
        }

        /// <summary>
        /// 执行无返回SQL，需要手工关闭连接
        /// </summary>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">命令</param>
        /// <param name="conn">事务连接</param>
        /// <param name="cmdParms">参数</param>
        /// /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlConnection conn, params SqlParameter[] cmdParms)
        {

            int val;
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                    val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                catch (SqlException)
                {                    
                    throw;
                }
            }
            return val;
        }

        /// <summary>
        /// 执行无返回SQL，需要手工关闭连接
        /// </summary>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">SQL指令</param>
        /// <param name="conn">事务连接</param>
        /// <param name="tran">事务对象</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlTransaction tran, params SqlParameter[] cmdParms)
        {

            int val;
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, tran.Connection, cmdType, cmdText, cmdParms);
                    cmd.Transaction = tran;
                    val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                catch (SqlException)
                {                    
                    throw;
                }
            }
            return val;
        }
        /// <summary>
        /// 执行无返回SQL
        /// </summary>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">命令</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnString);
            int val;
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms);
                val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            return val;
        }

        /// <summary>
        /// 执行无返回SQL
        /// </summary>      
        /// <param name="cmdText">命令</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteNonQuery(CommandType.Text, cmdText, cmdParms);
        }

        /// <summary>
        /// 执行无返回SQL
        /// </summary>      
        /// <param name="cmdText">命令</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(SqlTransaction tran, string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteNonQuery(CommandType.Text, cmdText, tran, cmdParms);
        }

        /// <summary>
        /// 获得连接
        /// </summary>
        /// <returns>连接对象</returns>
        public SqlConnection GetConnection()
        {
            SqlConnection conn = new SqlConnection(ConnString);
            return conn;
        }

        #region
        private void PrepareCommand(SqlCommand cmd, SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        #endregion
    }
}